エクセルファイルデータを Dataprep で BigQuery にロードして、SQL で参照してみた
こんにちは、みかみです。
エクセルファイルは手軽に使えて集計やグラフ表示など様々な操作が可能なので、業務でデータ管理ツールとして利用されることも多いのではないかと思います。
エクセルで管理しているデータを分析に利用しようと考えた場合、データを BigQuery などの DWH にロードして SQL を実行したくなるのではないでしょうか。
Cloud Dataprep は、GCP 管理コンソールから、GUI 操作でデータの抽出、加工、ロードが簡単に行えるデータプレパレーションツールで、インポートデータには Google スプレッドシートを指定することが可能です。
今回は、エクセルファイルをスプレッドシートに変換してから、Dataprep を使って整形処理をした後、BigQuery にロードしてみました。
やりたいこと
- エクセルデータを BigQuery にロードして、SQL で参照したい
はじめに
GCP のアカウントと課金が有効になプロジェクトは作成済みです。
動作確認に使用するデータは、以下のサイトから頂いてきた「オリンピック120年の歴史」のエクセルデータです。
このエクセルファイルを Google スプレッドシートに変換してから、 Dataprep で BigQuery にロードします。
エクセルファイルを Google スプレッドシートに変換
エクセルファイルを Google ドライブにアップロードしました。
アップロードしたファイルをダブルクリックで開き、画面上部の「Google スプレッドシートで開く」ボタンをクリック。
少し待つと、エクセルファイルがスプレッドシートで開きます。 「ファイル」タブ「Google スプレッドシートとして保存」をクリックして、スプレッドシートに変換します。
スプレッドシート画面からも、エクセルファイルがスプレッドシートに変換されたことが確認できました。
Dataprep でスプレッドシートデータをロード＆加工
2020/11 現在、Dataprep ではインプットデータとして以下の 4 種類を指定できます。
- Upload
- GCS
- Google Sheets
- BigQuery
Dataprep ダッシュボード「Import Data」ボタンから、スプレッドシートデータをインポートします。
インポート画面左のナビゲーションパネルで Google Sheets を選択し、ポップアップでスプレッドシートへのアクセスを許可した後、対象のファイルを指定します。
ライブラリ画面でインポートデータが追加されたことを確認できたら、フローを作成します。
「Create」ボタン「Create Flow」を選択して、任意のフロー名を入力し、「Add Datasets」ボタンからスプレッドシートのデータをフローに追加しました。
Dataset コンポーネント右横のプラスアイコンから「Add new Recipe」でレシピを追加して、データ整形処理を追加します。
「NOC」項目「Filter rows」メニューから、選手の所属国が日本（JPN）のレコードのみに絞ります。
また、各列の無効値（NA）を空値に変換する処理を追加しました。
追加した加工処理は右パネルで確認することができます。
ジョブを実行して整形したデータを BigQuery にロード
フロー画面でレシピコンポーネント右横のプラスアイコンから、「Create Output to run」を選択して、アウトプットのコンポーネントを追加します。
デフォルトの出力先は GCS になっているので、「Edit」リンクをクリックして BigQuery に変更します。
「Actions」の「Create-CSV」リンクをクリックすると出力先選択画面に遷移できるので、左のパネルから「BigQuery」を選択。 出力先のデータセットを選択し、「Create a new table」ボタンで新規作成するテーブル名とデータロードのモードを選択して「Update」します。
「Save Settings」ボタンで変更を保存したら、「Run Job」ボタンからジョブを実行します。
右パネルに表示されるジョブのリンクから詳細画面に遷移することができ、ジョブの実行状況を確認できます。 また、「View Dataflow job」リンクをクリックすると、Dataflow 画面のジョブグラフやログを確認することもできます。
ジョブが正常に実行できたことを確認して、BigQuery のデータを確認してみます。
BigQuery にテーブルが作成され、データがロードされています。
BigQuery 管理画面のクエリエディタから SQL を実行して、各年度ごとのメダルの数を取得してみます。
エクセルファイルのデータを BigQuery にロードして、無事 SQL で参照することができました。
まとめ（所感）
Dataprep を利用して、システム構築やプログラミングなどの手間なく、画面 GUI 操作だけでエクセルファイルを BigQuery にロードすることができました。
Dataprep は GCP の課金設定済みプロジェクトさえあれば、サードパーティ製のツールの導入などの手間なくブラウザからのアクセスだけですぐに利用開始することができ、必要なデータの前処理も GUI 操作で簡単に追加することができます。
エクセルファイル上でもマクロやアドインなどを利用してデータ分析を行うことはできますが、SQL を利用して分析する必要が生じた場合、 Dataprep を利用すると便利ではないかと思いました。
